//*********************************************************
//
//    Copyright (c) Microsoft. All rights reserved.
//    This code is licensed under the Apache License, Version 2.0.
//    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
//    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
//    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
//    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
//
//*********************************************************

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.AnalysisServices;
using System.Data.SqlClient;
using System.Data;


#endregion

namespace Microsoft.AnalysisServices.Extensions
{
    public class SQLServerCubeGenerator
    {

        protected String strDataSourceName;
        protected String strOlapDBName;
        protected String strSQLDBName;
        protected String strFactTable;
        protected String strDimensionTable;
        protected String strCubeName;
        protected String strMeasureColumns;

        Microsoft.AnalysisServices.Server analysisServer;
        Microsoft.AnalysisServices.Database olapDB;
        RelationalDataSource relDataSource;
        SqlDataAdapter objDataAdapter;
        DataSourceView dsView;
        DataSet objRelationsDS;
        Dimension[] objDimensions;

        /// <summary>
        /// Get and set the DataSourceName
        /// </summary>
        public string DataSourceName
        {
            get
            {
                return strDataSourceName;
            }
            set
            {
                strDataSourceName = value;
            }
        }

        /// <summary>
        /// Get and Set the OlapDBName.
        /// </summary>
        public string OlapDBName
        {
            get
            {
                return strOlapDBName;
            }
            set
            {
                strOlapDBName = value;
            }
        }

        /// <summary>
        /// Get and set SQLDB Name
        /// </summary>
        public string SQLDBName
        {
            get
            {
                return strSQLDBName;
            }
            set
            {
                strSQLDBName = value;
            }
        }

        /// <summary>
        /// Get and Set Fact Table Name.
        /// </summary>
        public string FactTableName
        {
            get
            {
                return strFactTable;
            }
            set
            {
                strFactTable = value;
            }
        }

        /// <summary>
        /// Get and Set Multiple Dimension Tables with Comma Seperated value.
        /// </summary>
        public string DimensionTableName
        {
            get
            {
                return strDimensionTable;
            }
            set
            {
                strDimensionTable = value;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public string CubeName
        {
            get
            {
                return strCubeName;
            }
            set
            {
                strCubeName = value;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public string MeasureColumns
        {
            get
            {
                return strMeasureColumns;
            }
            set
            {
                strMeasureColumns = value;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public Boolean GenerateCube()
        {
            //Add Validation

            //Connect to Analysis Service
            if (ConnectAnalysisService())
            {
                //Create DataBase 
                CreateOlapDB();
                //Create Relational Data Source
                CreateOlapDBDataSource();
                //Create Data Source View
                CreateOlapDataSourceView();

                objDimensions = new Dimension[strDimensionTable.Split(',').Length];
                objDimensions = (Dimension[])CreateDimension();

                CreateCube(objDimensions);
                return true;
            }


            return false;


        }

        /// <summary>
        /// Connect to the Analysis Services.
        /// </summary>
        /// <returns>True if connection take place properly else false.</returns>
        private Boolean ConnectAnalysisService()
        {
            try
            {

                analysisServer = new Microsoft.AnalysisServices.Server();
                string OLAPCONNECTIONSTRING = "Data Source=" + strDataSourceName + ";Provider=msolap;";

                if (!analysisServer.Connected)
                {
                    analysisServer.Connect(OLAPCONNECTIONSTRING);
                }
            }
            catch (Exception exAnalysisService)
            {
                System.Diagnostics.Trace.WriteLine("Could Not Connect to Analysis Server" + exAnalysisService.Message);
                return false;
            }
            return true;
        }

        /// <summary>
        /// Create the new Olap Database based on the name given.
        /// </summary>
        /// <returns>True if able to commit the database to Analysis Service else false.</returns>
        private Boolean CreateOlapDB()
        {
            try
            {
                olapDB = new Microsoft.AnalysisServices.Database();
                olapDB = analysisServer.Databases.Add(analysisServer.Databases.GetNewName(strOlapDBName));
                //Commit/Save to Analysis Service
                olapDB.Update();
            }
            catch (Exception exolapDB)
            {
                System.Diagnostics.Trace.WriteLine("Could Not add OlapDB to Analysis Server" + exolapDB.Message);
                throw exolapDB;
            }
            return false;
        }

        /// <summary>
        /// Create Relational DataSource based on the SQL DB
        /// </summary>
        /// <returns></returns>
        private Boolean CreateOlapDBDataSource()
        {
            try
            {
                relDataSource = new RelationalDataSource();
                relDataSource = olapDB.DataSources.Add(olapDB.DataSources.GetNewName(strSQLDBName + "DS"));
                string DATASOURCECONNECTIONSTRING = "Provider=SQLNCLI.1;Data Source=" + strDataSourceName + ";Integrated Security=SSPI;Initial Catalog=" + strSQLDBName;
                relDataSource.ConnectionString = DATASOURCECONNECTIONSTRING;
                relDataSource.Update();
            }
            catch (Exception exRelDataSource)
            {
                System.Diagnostics.Trace.WriteLine("Could Not create Olap Data Source to Analysis Server" + exRelDataSource.Message);
                return false;
            }
            return true;
        }

        /// <summary>
        /// Create OlapDataSourceView and Generate Schema
        /// </summary>
        /// <returns></returns>
        private Boolean CreateOlapDataSourceView()
        {
            try
            {
                dsView = new DataSourceView();
                dsView = olapDB.DataSourceViews.Add(olapDB.DataSourceViews.GetNewName(strSQLDBName + "DSView"));
                dsView.DataSourceID = relDataSource.ID;

                DataSet objDataset = GenerateSchemaObject();
                dsView.Schema = objDataset;
                dsView.Update();

            }
            catch (Exception exOlapDataSourceView)
            {
                System.Diagnostics.Trace.WriteLine("Could Not create DataSource View in Analysis Server" + exOlapDataSourceView.Message);
                return false;
            }
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private DataSet GenerateSchemaObject()
        {
            String connectionString = "Server=" + strDataSourceName + ";Database=" + strSQLDBName + ";Integrated Security=SSPI";

            DataSet objDataSet = new DataSet();
            //objRelationsDS = new DataSet();

            //Add Fact table to Dataset
            objDataSet = (DataSet)FillDataset(strFactTable, objDataSet, connectionString);

            for (int i = 0; i < strDimensionTable.Split(',').Length; i++)
            {
                //Retrieve table's schema and assign the table's schema to the DataSet.
                //Add primary key to the schema according to the primary key in the tables.
                objDataSet = (DataSet)FillDataset(strDimensionTable.Split(',').ElementAt(i).ToString(), objDataSet, connectionString);
            }


            StringBuilder objDimension = new StringBuilder();
            string[] objarrDimension = strDimensionTable.Split(',');
            for (int cnt = 0; cnt < objarrDimension.Length; cnt++)
            {
                if (cnt == 0)
                    objDimension.Append("'" + objarrDimension.GetValue(cnt) + "'");
                else
                    objDimension.Append(",'" + objarrDimension.GetValue(cnt) + "'");

            }

            objRelationsDS = GetForeignkeyRelations(connectionString, objDimension.ToString());

            //Add Relations to the Dataset
            AddRelations(objRelationsDS, objDataSet);
            return objDataSet;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strTableName"></param>
        /// <param name="objDataSet"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        private DataSet FillDataset(String strTableName, DataSet objDataSet, String connectionString)
        {
            try
            {
                objDataAdapter = new SqlDataAdapter("select * from " + strTableName, connectionString);
                objDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                objDataAdapter.FillSchema(objDataSet, SchemaType.Source, strTableName);

            }
            catch (Exception exFillDataset)
            {
                System.Diagnostics.Trace.WriteLine("Could Not create DataSource View in Analysis Server" + exFillDataset.Message);
                return null;

            }
            return objDataSet;
        }

        /// <summary>
        /// Get the Foreign relations with Fact table based on the Dimension Tables
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        private DataSet GetForeignkeyRelations(String connectionString, String strDimensionTable)
        {
            try
            {
                DataSet objRelationsInfo = new DataSet();
                StringBuilder objRelationQuery = new StringBuilder();

                objRelationQuery.Append("SELECT CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME, TABLE_CATALOG = FK.TABLE_CATALOG,TABLE_NAME = FK.TABLE_NAME,");
                objRelationQuery.Append(" COLUMN_NAME = FK_COLS.COLUMN_NAME,REFERENCED_TABLE_NAME = PK.TABLE_NAME,REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME");
                objRelationQuery.Append(" FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK");
                objRelationQuery.Append(" ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA");
                objRelationQuery.Append(" AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME AND FK.CONSTRAINT_TYPE = 'FOREIGN KEY'");
                objRelationQuery.Append(" INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG");
                objRelationQuery.Append(" AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME");
                objRelationQuery.Append(" AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY' INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME");
                objRelationQuery.Append(" INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME");
                objRelationQuery.Append(" Where PK.Table_Name in (" + strDimensionTable + ") and FK.TABLE_NAME ='" + strFactTable + "'");

                objDataAdapter = new SqlDataAdapter(objRelationQuery.ToString(), connectionString);
                objDataAdapter.Fill(objRelationsInfo, "RelationalInfo");
                //objDataAdapter.Fill((objRelationsInfo, SchemaType.Source, objRelationQuery.ToString());

                return objRelationsInfo;
            }
            catch (Exception exForeignRelations)
            {
                System.Diagnostics.Trace.WriteLine("Could Not create DataSource View in Analysis Server" + exForeignRelations.Message);
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="objRelationsDS"></param>
        /// <param name="objDataSet"></param>
        private void AddRelations(DataSet objRelationsDS, DataSet objDataSet)
        {
            for (int dsCnt = 0; dsCnt < objRelationsDS.Tables[0].Rows.Count; dsCnt++)
            {
                if (objRelationsDS.Tables[0].Rows[dsCnt]["Table_Name"].ToString() == strFactTable)
                {

                    //String objKeyRelations = (String)objRelationsDS.Tables[0].Rows[dsCnt]["CONSTRAINT_NAME"];
                    String objRefTableName = objRelationsDS.Tables[0].Rows[dsCnt]["REFERENCED_TABLE_NAME"].ToString();
                    String objRefTableColumnName = objRelationsDS.Tables[0].Rows[dsCnt]["REFERENCED_COLUMN_NAME"].ToString();
                    String objChildColumnName = objRelationsDS.Tables[0].Rows[dsCnt]["COLUMN_NAME"].ToString();
                    objDataSet.Relations.Add(objRelationsDS.Tables[0].Rows[dsCnt]["CONSTRAINT_NAME"].ToString(),
                                          objDataSet.Tables[objRefTableName].Columns[objRefTableColumnName],
                                          objDataSet.Tables[strFactTable].Columns[objChildColumnName]);
                }

            }
        }

        #region 5. Create Dimensions and Hierarchies

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private object[] CreateDimension()
        {
            try
            {
                Dimension[] objDimensions = new Dimension[objRelationsDS.Tables[0].Rows.Count];
                for (int i = 0; i < objRelationsDS.Tables[0].Rows.Count; i++)
                {
                    if (objRelationsDS.Tables[0].Rows[i]["Table_Name"].ToString() == strFactTable)
                    {
                        objDimensions[i] = (Dimension)GenerateDimension(objRelationsDS.Tables[0].Rows[i]["REFERENCED_TABLE_NAME"].ToString(), objRelationsDS.Tables[0].Rows[i]["REFERENCED_COLUMN_NAME"].ToString(), GetColumns(objRelationsDS.Tables[0].Rows[i]["REFERENCED_TABLE_NAME"].ToString()));
                    }
                }

                return objDimensions;
            }
            catch (Exception exCreateDimension)
            {
                System.Diagnostics.Trace.WriteLine("Could Not create DataSource View in Analysis Server" + exCreateDimension.Message);
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strTableName"></param>
        /// <param name="strTableKeyName"></param>
        /// <returns></returns>
        private object GenerateDimension(string strTableName, string strTableKeyName, DataTable strTableValuesName)
        {
            try
            {
                Dimension objDimension = new Dimension();

                //Add Dimension to the Database
                objDimension = olapDB.Dimensions.Add(strTableName);
                objDimension.Source = new DataSourceViewBinding(dsView.ID);
                DimensionAttributeCollection objDimensionAttributesColl = objDimension.Attributes;
                //Add Dimension Attributes
                foreach (DataRow drColumn in strTableValuesName.Rows)
                {
                    DimensionAttribute objAttribute = objDimensionAttributesColl.Add(drColumn[0].ToString());
                    //Set Attribute usage and source
                    objAttribute.Usage = AttributeUsage.Key;
                    objAttribute.KeyColumns.Add(strTableName, strTableKeyName, System.Data.OleDb.OleDbType.Integer);
                    objAttribute.NameColumn = new DataItem(strTableName, drColumn[0].ToString());
                    objAttribute.ValueColumn = new DataItem(strTableName, drColumn[0].ToString());
                }

                objDimension.Update();

                return objDimension;
            }
            catch (Exception exGenerateDimension)
            {
                System.Diagnostics.Trace.WriteLine("Could Not create DataSource View in Analysis Server" + exGenerateDimension.Message);
                return null;
            }
        }

        #endregion

        #region Create Cube
        private Boolean CreateCube(Dimension[] objDimensions)
        {
            Cube cube = olapDB.Cubes.Add(strCubeName); //new Cube();


            cube.Source = new DataSourceViewBinding(dsView.ID);
            cube.StorageMode = StorageMode.Molap;


            //Add Mesaure Group to the cube
            MeasureGroup mesGroup = cube.MeasureGroups.Add(strFactTable);

            #region Measures of Fact Table
            //Add Measures to Measure Group 

            for (int cnt = 0; cnt < strMeasureColumns.Split(',').Length; cnt++)
            {
                AddMeasureColumns(mesGroup, strFactTable, strMeasureColumns.Split(',').GetValue(cnt).ToString());
            }


            for (int dimensionCnt = 0; dimensionCnt < objRelationsDS.Tables[0].Rows.Count; dimensionCnt++)
            {
                if (objRelationsDS.Tables[0].Rows[dimensionCnt]["Table_Name"].ToString() == strFactTable)
                {
                    GenerateCubeDimension(cube, objDimensions[dimensionCnt], mesGroup, strFactTable, objRelationsDS.Tables[0].Rows[dimensionCnt]["COLUMN_NAME"].ToString());
                }
            }

            Partition objPartition = mesGroup.Partitions.Add(strFactTable);
            objPartition.Source = new TableBinding(relDataSource.ID, "dbo", strFactTable);
            objPartition.ProcessingMode = ProcessingMode.Regular;
            objPartition.StorageMode = StorageMode.Molap;

            //Save Cube and all major objects to the Analysis Services
            cube.Update(UpdateOptions.ExpandFull);

            olapDB.Process(ProcessType.ProcessFull);
            return true;

            #endregion




        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="mesGroup"></param>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>

        private void AddMeasureColumns(MeasureGroup mesGroup, string tableName, string columnName)
        {
            Measure objMeasures = new Measure();
            objMeasures = mesGroup.Measures.Add(columnName);
            //set Measure source
            objMeasures.Source = CreateDataItem(dsView, tableName, columnName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        private static DataItem CreateDataItem(DataSourceView dsv, string tableName, string columnName)
        {
            DataTable dataTable = ((DataSourceView)dsv).Schema.Tables[tableName];
            DataColumn dataColumn = dataTable.Columns[columnName];
            return new DataItem(tableName, columnName,
                OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="objCube"></param>
        /// <param name="objDimension"></param>
        /// <param name="objMeasureGroup"></param>
        /// <param name="strFactTableName"></param>
        /// <param name="strTableKey"></param>
        private static void GenerateCubeDimension(Cube objCube, Dimension objDimension, MeasureGroup objMeasureGroup, string strFactTableName, string strTableKey)
        {
            try
            {
                CubeDimension objCubeDim = new CubeDimension();
                RegularMeasureGroupDimension objRegMGDim = new RegularMeasureGroupDimension();
                MeasureGroupAttribute objMGA = new MeasureGroupAttribute();
                //Add Dimension to the Cube

                objCubeDim = objCube.Dimensions.Add(objDimension.ID);
                //Use Regular Relationship Between Dimension and FactTable Measure Group
                objRegMGDim = objMeasureGroup.Dimensions.Add(objCubeDim.ID);
                //Link TableKey in DimensionTable with TableKey in FactTable Measure Group
                objMGA = objRegMGDim.Attributes.Add(objDimension.KeyAttribute.ID);
                objMGA.Type = MeasureGroupAttributeType.Granularity;
                objMGA.KeyColumns.Add(strFactTableName, strTableKey, System.Data.OleDb.OleDbType.Integer);
            }
            catch (Exception exCubeDM)
            {
                System.Diagnostics.Trace.WriteLine("Could Not create DataSource View in Analysis Server" + exCubeDM.Message);
                throw exCubeDM;
            }


        }
        #endregion

        private DataTable GetColumns(string strTableName)
        {
            try
            {
                String connectionString = "Server=" + strDataSourceName + ";Database=" + strSQLDBName + ";Integrated Security=SSPI";
                SqlConnection objConnection = new SqlConnection(connectionString);
                string strCommand = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '" + strTableName + "'";
                DataSet objDataSet = new DataSet();
                SqlDataAdapter objEmpData = new SqlDataAdapter(strCommand, objConnection);
                objEmpData.Fill(objDataSet, strTableName);
                return objDataSet.Tables[strTableName];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
    }
}
